from utils import read_sql, df_into_db
import pandas as pd
import os


# 计算综合资金费率
funding_rate_list = []
df = read_sql("select * from coinglass_funding_rate", db_name="funding_rate")
for symbol in ['BTC-USDT', 'BTC-USD']:
    funding_rate_data = df[df['symbol'] == symbol]
    exchange_list = [i for i in funding_rate_data.columns if
                     i not in ['id', 'symbol', 'datetime', 'timestamp', 'price', 'date', 'time']]
    funding_rate_data['composite_funding_rate'] = funding_rate_data[exchange_list].mean(axis=1)

    timezone = '+0000'
    funding_rate_data['date'] = pd.to_datetime(funding_rate_data['timestamp'] - 1, unit='s').dt.tz_localize(
        'UTC').dt.tz_convert(timezone)
    funding_rate_data['date'] = funding_rate_data['date'].dt.strftime('%Y-%m-%d')
    funding_rate_data['datetime'] = pd.to_datetime(funding_rate_data['timestamp'] - 1, unit='s').dt.tz_localize(
        'UTC').dt.tz_convert(timezone)
    funding_rate_data['datetime'] = funding_rate_data['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
    funding_rate_data['time'] = pd.to_datetime(funding_rate_data['timestamp'] - 1, unit='s').dt.tz_localize(
        'UTC').dt.tz_convert(timezone)
    funding_rate_data['time'] = funding_rate_data['time'].dt.strftime('%H:%M:%S')

    funding_rate = funding_rate_data.groupby('date')['composite_funding_rate'].mean()
    funding_rate.index.name = 'end_date'
    funding_rate_list.append(funding_rate)
    # to db
    funding_rate_df = funding_rate.to_frame()
    funding_rate_df = funding_rate_df.reset_index(drop=False)
    funding_rate_df['symbol'] = symbol
    funding_rate_df.rename(columns={'composite_funding_rate': 'DIY_Futures_Perpetual_Funding_Rate'}, inplace=True)
    df_into_db(funding_rate_df, db_name="funding_rate", table_name="diy_futures_perpetual_funding_rate")
funding_rate_diff = funding_rate_list[0] - funding_rate_list[1]
funding_rate_diff = funding_rate_diff[funding_rate_diff[~funding_rate_diff.isnull()].index[0]:]  # 剔除因为相减导致的NAN
funding_rate_diff.name = f'DIY_Futures_Perpetual_Funding_Rate_Diff'

funding_rate_plus = pd.concat(funding_rate_list, axis=1)
funding_rate_plus.sort_index(inplace=True)
funding_rate_plus['plus'] = funding_rate_plus.mean(axis=1)
funding_rate_plus = funding_rate_plus['plus']
funding_rate_plus.name = f'DIY_Futures_Perpetual_Funding_Rate_Plus'
df = pd.concat([funding_rate_diff, funding_rate_plus], axis=1)
df = df.reset_index(drop=False)
df_into_db(df, db_name="funding_rate", table_name="DIY_Futures_Perpetual_Funding_Rate_DIFF_PLUS")